<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1" 
    import="web.rec.*,java.sql.*,java.util.Formatter,java.util.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="style/mainstyle.css" />
<style type="text/css" >
div {
	display: inline-block;
}

.ssn {


}

#ssn_select {
	width: 400px;
}
#output div {
	width: 300px;
}

</style>

</head>
<body>
<div id="menu">
<div><a href="mainPage.jsp">Main Page</a></div><br />
	<%
		Map<String,String[]> map = ResourceTool.menuMap;
		String popup = request.getParameter("popup");
		
		if(popup == null || popup.equals("false")){
		
			for(String key: map.keySet()){
		%>
		<div>
			<h3><%= key %></h3>
			<%= ResourceTool.getMenu(key) %>
		</div><br />
		<%
			}
		}
	%>
</div>
<div id='content'>
<h1>Class Schedule Report</h1>


<%
	String action = request.getParameter("action");
	Connection conn = ResourceTool.getConnection();
	Statement stmt = conn.createStatement();	
	ResultSet rs;
	
	if( action == null || !action.equals("query") )
	{
		
%>
<form action='ClassScheduleReport.jsp' method='get'>
<%		
		rs = stmt.executeQuery("SELECT ssn, first_name, mid_name, last_name FROM StudentQuarterEnrolled sqe, Student s WHERE s.student_id = sqe.student_id"+
				" AND sqe.year = "+ 2012 +" AND sqe.season = 'winter'" +
				" ORDER BY last_name");
		
		
		StringBuilder sb = new StringBuilder(200);
		Formatter fmt = new Formatter(sb);
		
		String ssn, first, mid, last;
		
		int ssn_width = 20, fname_width = 10, lname_width = 10;
		
		while(rs.next())
		{
			ssn = rs.getString("ssn");
			first = rs.getString("first_name");
			mid = rs.getString("mid_name");
			last = rs.getString("last_name");
			
			sb.append("<option> " + ssn);
			for(int i = ssn.length() ; i < ssn_width ; i++)
				sb.append("&nbsp;");
			
			sb.append(last + ",");
			for(int i =last.length() ; i < lname_width ; i++)
				sb.append("&nbsp;");
			
			sb.append(first);
			for(int i = first.length() ; i < fname_width ; i++)
				sb.append("&nbsp;");
			
			sb.append(mid);
			sb.append("</option>");
			
		}
		out.append("<div style='width:200px'><b>ssn</b></div><div><b>name (last,first mi)</b></div><br />");
		out.append("<select name='ssn' size='20' id='ssn_select'>");
		out.append(fmt.toString());
		out.append("</select>");
		
	

%>
<br />
<input type="hidden" name="action" value="query" />
<input type="submit" value="submit" />
</form>
<%
}
	else if(action.equals("query"))
	{
		Map<String, String[]> paramMap = request.getParameterMap();
		
		for(String s: paramMap.keySet()){
			System.out.print(s + ": ");
			System.out.print(paramMap.get(s)[0]);
		}
		System.out.println();
		
	String inputStr  = request.getParameter("ssn");
	
	inputStr.split("\\u00A0+|\\s+");
	String [] inputArr = inputStr.split("\\u00A0+|\\s+");
	String ssn = inputArr[0];
	
	
	System.out.println("ssn: " + ssn);
	
	String qry = "SELECT DISTINCT m.section_id, m.start_time, m.end_time FROM Student stu, StudentTakingSection sts, Section sec, Meeting m, Class c"+
			" WHERE stu.student_id = sts.student_id AND stu.ssn ='" + ssn + "'" +
			" AND sts.section_id = sec.section_id AND sec.class_id = c.class_id AND c.season='winter'" + 
			" AND c.year=" + 2012 +" AND sec.section_id = m.section_id ORDER BY section_id;";
			
	System.out.println(qry);		
	rs = stmt.executeQuery(qry);
	
	
	ResultSetMetaData rsmd = rs.getMetaData();
	
	Map<String, Integer> colIndex = new HashMap<String, Integer>();
	Map<Integer, ArrayList<String>> currentClasses = new HashMap<Integer, ArrayList<String>>();
	for(int i = 1; i <= rsmd.getColumnCount(); i++){
		String name = rsmd.getColumnName(i);
		colIndex.put(name, i);
	}
	
	int rowIndex = 0;
	while(rs.next())
	{
		ArrayList<String> rowData = new ArrayList<String>();
		
		for(int i = 1; i <= rsmd.getColumnCount() ; i++)
		{
			rowData.add(rs.getString(i));
			
			
		}
		currentClasses.put(rowIndex, rowData);
		rowIndex++;
		
	}
	
	int section_id = colIndex.get("section_id");
	int start_time = colIndex.get("start_time");
	int end_time = colIndex.get("end_time");
	//int weekday = colIndex.get("weekday");
	
	out.append("<div id='section_info'>");
	
	Set<String> conflictingSections = new HashSet<String>();
	
	String class_id = "", curr_class = "";
	String sec_id = "", curr_sec = "";
	String weekday_out = "";
	
	Map<String, Integer> sectionCount = new HashMap<String,Integer>();
	
	
	for(ArrayList<String> rowList: currentClasses.values() )
	{
		qry = "SELECT c.class_id FROM Class c,Section s " +
				"WHERE s.class_id = c.class_id AND s.section_id = '" + rowList.get(section_id -1)+ "'" +
				" AND c.season='winter' AND c.year= 2012 ";
		System.out.println(qry);
		rs = stmt.executeQuery(qry);
		rs.next();
		class_id = rs.getString("class_id");
		
		System.out.println("class: "+ class_id + "  section: " + rowList.get(section_id -1 ));
		

		
		if(!class_id.equals(curr_class)){
			curr_class = class_id;
			out.append("<div>" + class_id+ "</div>");
			
		}
		
		
		sec_id = rowList.get( section_id-1 );
		if(sec_id.equals("") || sec_id.equals(curr_sec)){
			sec_id = rowList.get( section_id-1 );
		}
		
		
		/*
		 * OLD QUERY
			qry = "select m.section_id from Meeting m where "+
					" m.start_time >= "+ rowList.get(start_time-1) +" AND "+ 
					" m.start_time <= " + (Integer.valueOf( rowList.get(start_time-1))+ 50) + 
					") OR " + "m.end_time <= " + rowList.get(end_time-1) + "' as datetime) AND " +
					"cast(m.end_time as datetime) >= cast('"+ rowList.get(start_time-1) +"' as datetime))) AND "+ 	
					" AND m.section_id <> '"+ rowList.get(section_id-1) +"';";
		 
		 
		 */
		
		qry = "select m.section_id from Meeting m where "+
				" ((m.start_time >= "+ rowList.get(start_time-1) +" AND "+ 
				" m.start_time <= " + rowList.get(end_time-1) + " ) OR (m.end_time <= " + rowList.get(end_time -1)+" AND "+ 
				"m.end_time >= " + rowList.get(start_time-1) + " ))" + 
				//" AND m.weekday = " + rowList.get( weekday-1 ) + 
				" AND m.section_id <> '"+ rowList.get( section_id-1 ) +"';";
		
		System.out.println(qry);
				
		rs = stmt.executeQuery(qry);
		
		while(rs.next()){
			conflictingSections.add(rs.getString(1));
		}
		
	}
	rowIndex = 0;
	currentClasses = new HashMap<Integer, ArrayList<String>>();
	
	ArrayList<String> header = new ArrayList<String>();
	header.add("title");
	header.add("class id");
	
	
	out.append("<div id='output'>");
	for(String s: header){
		out.append("<div><b>" + s +"</b></div>");
	}
	out.append("<br />");
	
	Map<String, ArrayList<String>> classToSectionMap = new HashMap<String, ArrayList<String>>();
	ArrayList<String> tmp;
	Set<String> conflictingClassSet = new HashSet<String>();
	
	Map<String, String> sectionToClassMap = new HashMap<String,String>();
	for(String section: conflictingSections){
		qry = "SELECT s.class_id FROM Section s INNER JOIN Class c ON s.class_id = c.class_id WHERE s.section_id='"+section+"'"+
				" AND c.season='winter' AND c.year= 2012 " ;
		rs = stmt.executeQuery(qry);
	//
		rs.next();
		
		String cid = rs.getString("class_id");
		sectionToClassMap.put(section, cid);
		
		conflictingClassSet.add(cid);
	
	}
	
	for(String cid: conflictingClassSet){
		qry = "SELECT s.section_id FROM Section s INNER JOIN Class c ON s.class_id = c.class_id WHERE s.class_id='" +cid+ "'"+
				" AND c.season='winter' AND c.year= 2012" ;
		rs = stmt.executeQuery(qry);
		while(rs.next()){
			String sid = rs.getString("section_id");
			if(classToSectionMap.get(cid) == null){
				tmp = new ArrayList<String>();
				tmp.add(sid);
				classToSectionMap.put(cid,tmp);
			}
			else{
				tmp = classToSectionMap.remove(cid);
				tmp.add(sid);
				classToSectionMap.put(cid,tmp);
			}
		}
	}
	
	
	for(String section: conflictingSections){
		String cid = sectionToClassMap.get(section);
		classToSectionMap.get(cid).remove(section);
	}
	
	Set<String> classesCannotBeTaken = new HashSet<String>();
	
	for(String cid: classToSectionMap.keySet()){
		if(classToSectionMap.get(cid).isEmpty()){
			classesCannotBeTaken.add(cid);
		}
	}
	
/*
	
	for(String section: conflictingSections){
		rs = stmt.executeQuery("SELECT c.title, c.class_id FROM Class c, Section s "+
				"WHERE s.section_id='" + section + "' AND s.class_id=c.class_id");
		
		
		
		while(rs.next()){
			ArrayList<String> dataList = new ArrayList<String>();
			for(int i = 1 ; i <= rs.getMetaData().getColumnCount() ; i++){
				dataList.add(rs.getString(i));
				
				System.out.print(rs.getString(i) + " ");
			}
			System.out.println();
			currentClasses.put(rowIndex, dataList);
			rowIndex++;
		}
		
		
	}
*/
	
	Set<String> printed = new HashSet<String>();
	for(ArrayList<String> list : currentClasses.values()){
		boolean first = true;
		for(String s: list){
			if(!printed.contains(s))
				out.append("<div>" + s +"</div>");
			else
				break;
			
			if(first){
				printed.add(s);
				first = false;
			}
		}
		out.append("<br />");
	}
	out.append("</div>");
}
%>
</div>

</body>
</html>
